package cn.learn.parseSql;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLLimit;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLInSubQueryExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.util.JdbcConstants;
import org.junit.Test;

import java.util.List;

/**
 * 解析sql
 *
 * @author huangyezhan
 * @version 1.0
 * @date 2020年08月18日9:38
 */
public class StatementParser {

    String sql =
            "SELECT acc.id , us.account_id " +
                    "FROM tb_base_account acc " +
                    "LEFT JOIN tb_base_userinfo us ON acc.id = us.account_id " +
                    "LEFT JOIN tb_base_user_identity uid ON uid.account_id = acc.id " +
                    "LEFT JOIN tb_base_tax tax ON tax.id = uid.tax_id AND tax.useful = 1 AND tax.deleted = 0 " +
                    "WHERE acc.deleted = 0 ";

    @Test
    public void test1() throws  Exception{

        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement statement = parser.parseStatement();
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        statement.accept(visitor);


        System.out.println(visitor.getTables());
        System.out.println(visitor.getColumns());
        System.out.println(visitor.getConditions());        //条件（先确定预编译的展示问题）
        System.out.println(visitor.getGroupByColumns());    //group by
        System.out.println(visitor.getParameters());        //参数【预编译的那种，用？占位的】
        System.out.println(visitor.getRelationships());     //关联表的字段
        System.out.println(visitor.getAggregateFunctions());//sql聚合函数COUNT、MAX、SUM等
        System.out.println(visitor.getFunctions());         //round
        System.out.println(visitor.getOrderByColumns());    //排序字段
    }

    @Test
    public void test2() throws  Exception{
        String             mysql         = JdbcConstants.MYSQL;
        List<SQLStatement> sqlStatements = SQLUtils.parseStatements(sql, mysql);
        SchemaStatVisitor  statVisitor   = SQLUtils.createSchemaStatVisitor(mysql);
        //SQLStatement stmt = sqlStatements.get(0);
        //stmt.accept(statVisitor);
        //多条sql解析放进语法树里面
        for (SQLStatement stmt : sqlStatements) {
            stmt.accept(statVisitor);
        }

        System.out.println(statVisitor.getTables());
        System.out.println(statVisitor.getColumns());
        System.out.println(statVisitor.getConditions());        //条件（先确定预编译的展示问题）
        System.out.println(statVisitor.getGroupByColumns());    //group by
        System.out.println(statVisitor.getParameters());        //参数【预编译的那种，用？占位的】
        System.out.println(statVisitor.getRelationships());     //关联表的字段
        System.out.println(statVisitor.getAggregateFunctions());//sql聚合函数COUNT、MAX、SUM等
        System.out.println(statVisitor.getFunctions());         //round
        System.out.println(statVisitor.getOrderByColumns());    //排序字段
    }

    /**
     * https://www.cnblogs.com/lay2017/p/9840394.html
     *
     * https://github.com/alibaba/druid/wiki/Druid_SQL_AST#24-sqlselect--sqlselectquery
     * https://github.com/alibaba/druid/wiki/SQL-Parser
     * https://github.com/alibaba/druid/wiki/SQL-Parser
     *
     * @throws Exception 异常
     */
    @Test
    public void test3() throws  Exception{
        // 解析
        List<SQLStatement> statements = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
        // 只考虑一条语句
        SQLStatement statement = statements.get(0);
        // 只考虑查询语句
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;
        SQLSelectQuery     sqlSelectQuery     = sqlSelectStatement.getSelect().getQuery();
        // 非union的查询语句
        if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
            // 获取字段列表
            List<SQLSelectItem> selectItems         = sqlSelectQueryBlock.getSelectList();

            System.out.println(selectItems);

            selectItems.forEach(x -> {
                // 处理---------------------
            });
            // 获取表
            SQLTableSource table = sqlSelectQueryBlock.getFrom();
            // 普通单表
            if (table instanceof SQLExprTableSource) {
                // 处理---------------------
                // join多表
            } else if (table instanceof SQLJoinTableSource) {
                // 处理---------------------
                System.out.println(table.getAlias());
                System.out.println(table);
                // 子查询作为表
            } else if (table instanceof SQLSubqueryTableSource) {
                // 处理---------------------
            }
            // 获取where条件
            SQLExpr where = sqlSelectQueryBlock.getWhere();
            // 如果是二元表达式
            if (where instanceof SQLBinaryOpExpr) {
                SQLBinaryOpExpr   sqlBinaryOpExpr = (SQLBinaryOpExpr) where;
                SQLExpr           left     = sqlBinaryOpExpr.getLeft();
                SQLBinaryOperator operator = sqlBinaryOpExpr.getOperator();
                SQLExpr           right    = sqlBinaryOpExpr.getRight();
                // 处理---------------------
                // 如果是子查询
            } else if (where instanceof SQLInSubQueryExpr) {
                SQLInSubQueryExpr sqlInSubQueryExpr = (SQLInSubQueryExpr) where;
                // 处理---------------------
            }
            // 获取分组
            SQLSelectGroupByClause groupBy = sqlSelectQueryBlock.getGroupBy();
            // 处理---------------------
            // 获取排序
            SQLOrderBy orderBy = sqlSelectQueryBlock.getOrderBy();
            // 处理---------------------
            // 获取分页
            SQLLimit limit = sqlSelectQueryBlock.getLimit();
            // 处理---------------------
            // union的查询语句
        } else if (sqlSelectQuery instanceof SQLUnionQuery) {
            // 处理---------------------
        }
    }
}
